Amazon Redshift: テーブル権限付与設定(GRANT/REVOKE)がON ALL TABLES IN SCHEMA指定で一括実行出来る様になりました

#Amazon Redshift

#AWS

しんや

2014.10.25

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

以前のエントリでRedshiftのテーブルに対するアクセス権限付与設定について触れましたが、その当時は権限付与(GRANT)文/権限剥奪(REVOKE)文の対象が『テーブル1つ』だった為、仮にデータベーススキーマ内に10個テーブルがあった場合はその10個それぞれに対してSQL処理を実行しなければなりませんでした。

Amazon Redshift グループとユーザー、アクセス権の管理について｜ Developers.IO

ですが先日、Amazon Redshiftに対して告知されたリリースノードでこの部分が改善され、GRANT/REVOKE文の実行がより便利に・効率的に使えるようになりました。当エントリではその内容について御紹介したいと思います。

Release: Amazon Redshift on 2014-10-16 : Release Notes : Amazon Web Services

スキーマ内の全てのテーブルを対象とする『ON ALL TABLES IN SCHEMA』オプション

今回新たに追加されたのがこちらのオプション。スキーマ内の全てのテーブルに対して適用させると言うものです。一律『当該グループ/ユーザーに対して、スキーマ内の全てのテーブルに対して参照権限のみ付与させる』なんて時には一発で実行出来る訳です。

スキーマ・グループ・ユーザーの準備

まずは検証用に各種要素を用意します。実行は全てスーパーユーザー権限を持つユーザーでログイン後行いました。

/** スキーマの作成 */
# CREATE SCHEMA on_all_test;
CREATE SCHEMA

/** グループの作成 */
# CREATE GROUP on_all_group;
CREATE GROUP

/** ユーザーの作成　*/
# CREATE USER on_all_user1 WITH PASSWORD 'redSHIFT001';
CREATE USER
# CREATE USER on_all_user2 WITH PASSWORD 'redSHIFT002';
CREATE USER
# CREATE USER on_all_user3 WITH PASSWORD 'redSHIFT003';
CREATE USER

/** グループにユーザを作成 */
# ALTER GROUP on_all_group ADD USER on_all_user1;
ALTER GROUP
# ALTER GROUP on_all_group ADD USER on_all_user2;
ALTER GROUP
# ALTER GROUP on_all_group ADD USER on_all_user3;
ALTER GROUP

テーブルの作成

次いで、上記で作成したスキーマ内にテーブルを作成します。今回テーブルの中身は特に重要では無いので、同じ定義で別名のテーブルを複数個用意しておきます。

/** テーブルを作成 */
# CREATE TABLE on_all_test.TEST_TABLE_01 ( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL);
CREATE TABLE
# CREATE TABLE on_all_test.TEST_TABLE_02 ( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL);
CREATE TABLE
# CREATE TABLE on_all_test.TEST_TABLE_03 ( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL);
CREATE TABLE

Redshiftクラスタの再起動

上記作業でスキーマを追加している為、システムの情報を確認するために検索パスに該当スキーマ名を追記後、Redshiftクラスタを再起動する必要があります。以下エントリを参考に設定を行い、設定情報が以下の様に取得出来る事を確認します。

Amazon Redshiftで新規追加したスキーマを検索パスに含める｜ Developers.IO

# show search_path;
       search_path               
----------------------------
 $user, public, on_all_test
(1 row)

処理実行前の権限確認

今回対象となったSQL文を実行する前の内容を確認してみます。作成直後は何も権限が割り当てられていません。

# \z
                    Access privileges
   schema    |     name      | type  | access privileges 
-------------+---------------+-------+-------------------
 on_all_test | test_table_01 | table | 
 on_all_test | test_table_02 | table | 
 on_all_test | test_table_03 | table | 
(3 rows)

SQL文実行(GRANT文)

GRANT - Amazon Redshift

ドキュメントのシンタックスに倣ってSQLを実行してみます。グループ：on_all_groupに対し、スキーマ：on_all_test内の全てのテーブルに対する参照権限(SELECT)を付与してみました。

# GRANT SELECT ON ALL TABLES IN SCHEMA on_all_test TO GROUP on_all_group;
GRANT

処理実行後の権限確認

改めて該当テーブルの権限付与設定を確認してみます。ちゃんと参照権限が指定のテーブルに付与されていますね！

# \z
                        Access privileges
   schema    |     name      | type  |     access privileges     
-------------+---------------+-------+---------------------------
 on_all_test | test_table_01 | table | root=arwdRxt/root        +
             |               |       | group on_all_group=r/root
 on_all_test | test_table_02 | table | root=arwdRxt/root        +
             |               |       | group on_all_group=r/root
 on_all_test | test_table_03 | table | root=arwdRxt/root        +
             |               |       | group on_all_group=r/root
(3 rows)

追加(上書き)実行も問題無し

ちなみに既に追加されているテーブルにはそのままの値を、追加されていないテーブルには有効な更新を行うというオペレーションも以下の様に反映されていました。

# CREATE TABLE on_all_test.TEST_TABLE_101 ( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL);
CREATE TABLE
# CREATE TABLE on_all_test.TEST_TABLE_102 ( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL);
CREATE TABLE
testdb=# \z
                        Access privileges
   schema    |      name      | type  |     access privileges     
-------------+----------------+-------+---------------------------
 on_all_test | test_table_01  | table | root=arwdRxt/root        +
             |                |       | group on_all_group=r/root
 on_all_test | test_table_02  | table | root=arwdRxt/root        +
             |                |       | group on_all_group=r/root
 on_all_test | test_table_03  | table | root=arwdRxt/root        +
             |                |       | group on_all_group=r/root
 on_all_test | test_table_101 | table | 
 on_all_test | test_table_102 | table | 
(5 rows)

# GRANT SELECT ON ALL TABLES IN SCHEMA on_all_test TO GROUP on_all_group;
GRANT
# \z
                        Access privileges
   schema    |      name      | type  |     access privileges     
-------------+----------------+-------+---------------------------
 on_all_test | test_table_01  | table | root=arwdRxt/root        +
             |                |       | group on_all_group=r/root
 on_all_test | test_table_02  | table | root=arwdRxt/root        +
             |                |       | group on_all_group=r/root
 on_all_test | test_table_03  | table | root=arwdRxt/root        +
             |                |       | group on_all_group=r/root
 on_all_test | test_table_101 | table | root=arwdRxt/root        +
             |                |       | group on_all_group=r/root
 on_all_test | test_table_102 | table | root=arwdRxt/root        +
             |                |       | group on_all_group=r/root
(5 rows)

# GRANT INSERT ON ALL TABLES IN SCHEMA on_all_test TO GROUP on_all_group;
GRANT
# \z
                         Access privileges
   schema    |      name      | type  |     access privileges      
-------------+----------------+-------+----------------------------
 on_all_test | test_table_01  | table | root=arwdRxt/root         +
             |                |       | group on_all_group=ar/root
 on_all_test | test_table_02  | table | root=arwdRxt/root         +
             |                |       | group on_all_group=ar/root
 on_all_test | test_table_03  | table | root=arwdRxt/root         +
             |                |       | group on_all_group=ar/root
 on_all_test | test_table_101 | table | root=arwdRxt/root         +
             |                |       | group on_all_group=ar/root
 on_all_test | test_table_102 | table | root=arwdRxt/root         +
             |                |       | group on_all_group=ar/root
(5 rows)

まとめ

以上、Amazon Redshiftでの権限付与設定時の実行オプションに関するご紹介でした。スキーマ内テーブルでテーブル個別に設定を行う場合は指定も個別に行う必要がありますが、そうでない場合(一律設定で済む場合)はこのオプションを用いる事で大幅に処理が効率化出来るので使わない手は無いと思います。こちらからは以上です。